OpenEdge Development:<br />Progress 4GL Handbook Repositioning a query

Often you need to reposition a query other than to the first, last, next, or previous row. You might need to jump to a row based on data the user entered or return to a row that you previously saved off. Or you might want to jump forward or backward a specific number of rows to simulate paging through the query. You can do all these things with the REPOSITION statement, which has this syntax:


REPOSITION `query-name` { \|TO ROW `row-number` \|FORWARDS `n` \|BACKWARDS `n` \|TO ROWID `buffer-1-rowid` [, . . .] [ NO-ERROR ] }

The query-name in this case is not an expression. It can only be an unquoted query name, not a variable.

If you specify the TO ROW option followed by an integer expression, the query repositions to that sequential position within the results list. If you have previously saved off that position using the CURRENT-RESULT-ROW function, you can use the value that function returned as the value in the TO ROW phrase to reposition to that row.

If you use the FORWARDS or BACKWARDS phrase, you can jump forward or backward any number of rows, specified by the n integer expression. You can use the FORWARD or BACKWARD keywords instead of FORWARDS or BACKWARDS.

The last of the REPOSITION options requires an explanation of a Progress data construct you haven’t seen before.

Using a RowID to identify a record

Every record in every table of a database has a unique row identifier. (Technically, the row identifier is only unique within a single storage area of a database. Since an entire database table must be allocated to a single storage area, this effectively makes the identifier unique at least within that table. A discussion of database constructs such as storage areas is beyond the scope of this book.)

The identifier is called a RowID. There is both a ROWID data type that allows you to store a row identifier in a procedure variable and a ROWID function to return the identifier of a record from its record buffer.

Generally, you should consider a RowID to be a special data type without being concerned about its storage format. The RowID is (among other things) designed to be valid, not just for the OpenEdge database, but for all the different databases you can access from the 4GL using OpenEdge DataServers, which provide access from the 4GL to database types such as Oracle and Microsoft SQLServer.

In fact, you can’t display a RowID directly in a Progress 4GL procedure. If you try to, you get an error. You can see a RowID by converting it to a CHARACTER type using the STRING function. For instance, here is a procedure that shows you the RowIDs of the rows that satisfy the sample query you’ve been working with:

DEFINE QUERY CustQuery FOR Customer SCROLLING. OPEN QUERY CustQuery FOR EACH Customer WHERE State = "LA". GET FIRST CustQuery. DO WHILE NOT QUERY-OFF-END("CustQuery"): DISPLAY Customer.CustNum Customer.NAME CURRENT-RESULT-ROW("CustQuery") LABEL "Row#" STRING(ROWID(customer)) FORMAT "x(12)" LABEL "RowId" WITH FRAME CustFrame 15 DOWN. GET NEXT CustQuery. DOWN WITH FRAME CustFrame. END.

The RowID is displayed as a hexadecimal value. The values you would see in your own copy of the Sports2000 database might be different from these, and certainly they would be different if you modified the data, dumped it, and reloaded it into the database, because the RowID reflects the actual storage location of the data for the record, and this is not in any way predictable or necessarily repeatable. You should never count on a RowID as a permanent identifier for a record. However, you can use a RowID if you need to relocate a record you have previously retrieved within a procedure and whose RowID you saved off. This is what the TO ROWID phrase in the REPOSITION statement is for.

Even in this case, you must be aware that in the event of a record deletion, it is possible that a new record could be created that has the same RowID as the record that was deleted. So, even within a single session a RowID is not an absolutely reliable pointer to a record. In addition, RowIDs are unique only within a single database storage area. Therefore, the same RowID might occur for records in different tables that happen to be in different storage areas.

With these conditions in mind, you can use the TO ROWID phrase to reposition to a record in a query. Note that the RowID is for a particular database record, not an entire query row, so you need to save off the RowID of the record buffer, not of the query name, to reuse it. And in the case of a query with a join between tables, you need to save the RowID of each record buffer in order to reposition to it later and restore each of the records in the join.

The NO-ERROR option in this phrase lets you suppress an error message if the RowID turns out to be invalid for any reason. You could then use the AVAILABLE function or the ERROR-STATUS handle (see Chapter 17 "Managing Transactions") to determine whether the query was successfully repositioned.

There is another similar identifier in Progress called a RECID. This identifier was used in earlier versions of Progress to identify records in the same way as RowIDs do now. The RECID is essentially an integer identifier for a record, though it has its own data type. It is still supported but for several reasons (including, but not limited to, portability of code between database types that you can access with DataServers), it is strongly recommended that you use only the RowID form in new application code. The Progress 4GL continues to support RECIDs mainly for backward compatibility with older applications that still use them.

Positioning details with the REPOSITION statement

So if you execute a REPOSITION statement that repositions TO ROW 5 or FORWARDS 10 or TO ROWID rRow, then your procedure is positioned to that row so that you can display it or otherwise use it, right? Well, not exactly. When you use the REPOSITION statement, Progress positions the query in between records, so that you then have to execute a GET NEXT or GET PREV statement to position on a row so that you can actually use it. Here are some of the specifics:

When you execute a REPOSITION statement that uses the TO ROWID or TO ROW phrase, the query is positioned before the record requested. You then need to execute a GET NEXT statement to make the row you want available.

When you execute a REPOSITION query-name BACKWARDS statement, the query is positioned between records, and you need to execute a GET NEXT statement to make the row you intend available. For example, if the query is positioned to row 6 of a query results list and you execute a REPOSITION query-name BACKWARDS 2, then the query is positioned effectively between results list rows 3 and 4, and a GET NEXT statement makes row 4 available. A GET PREV statement makes row 3 available.

When you execute a REPOSITION statement with the FORWARDS phrase, you are actually positioned beyond the record you might expect. For example, if you are on row 6 and issue a statement with FORWARDS 2, then the query is positioned between row 8 and row 9, and you need to then execute a GET PREV statement to make row 8 available or a GET NEXT statement to make row 9 available.

Note as well that there is only one way for Progress to know which row is five rows ahead of the current row, or five rows behind it, or the fifth row of the result set, and that is to walk through the query row by row until it gets to the one you want. If the row in question has already been retrieved and is in the results list, then Progress can reposition to the row you want very quickly using the results list. Therefore, the REPOSITION statement with any of the options TO ROW, FORWARDS, or BACKWARDS maintains the integrity of the results list and the functions that use it. The REPOSITION TO ROWID statement also maintains the integrity of the list if the row you want has already been retrieved and the results list has not been flushed since you retrieved it, because Progress can scan the results list to locate the RowID.